#-*- coding:utf-8 -*-
"""
Module providing tools to manipulate data structures
Created on 08/11/11
by fccoelho
"""
__author__ = 'fccoelho'
__docformat__ = "restructuredtext en"

import pandas as pd
from sqlsoup import SQLSoup
from numbers import Number
import datetime
import os
import dbf
from itertools import count
from collections import OrderedDict, defaultdict
import monetdb.sql
import numpy as np


class DataViewer(object):
    """
    This class works a data proxy, providing a unified interface to multiple kinds of data-sources
    Internally it will used the dataframe class from Pandas to manipulate data.
    """
    def __init__(self, source, kind, **kwargs):
        """
        Initializes data viewer by creating a conectin with the data[base|set]
        :param source: filename or connection string
        :param kind: kind of database: sqlite|mysql|csv|dbf
        :param kwargs:
        """
        try:
            assert kind in _supported_kinds
        except AssertionError:
            raise AssertionError('Unsupported database')
        self.dbconnector = _supported_kinds[kind](source, **kwargs)
        self.source = source
        self.kind = kind

    def get_data(self, table, slice):
        return self.dbconnector.get_data_frame(table)

    def get_var_types(self, table, kind):
        if kind == "sql":
            sql = 'SELECT * FROM %s LIMIT 1' % table
            q = self.currentdb.bind.execute(sql)
            vanNames = vnames = q.keys()
            varTypes = []
            res = q.fetchone()
            if res != None:
            #only if there are rows in the table
                for i, n in zip(res, vnames):
                    if isinstance(i, Number):
                        varTypes.append(Number)
                    else:
                        varTypes.append(unicode)
            self.currentdb.flush()
        elif kind == "csv":
            varNames = vnames = self.currentdb.columns.tolist()
            varTypes = []
            for v in vnames:
                if isinstance(self.currentdb[v][0], Number):
                    varTypes.append(Number)
                else:
                    varTypes.append(unicode)
            self.currentVarTypes = dict(zip(vnames,self.currentdb.dtypes.tolist()))
        elif kind == "dbf":
            self.currentdb.open()
            vanNames = vnames = self.currentdb.field_names
            varTypes = []
            for rec in self.currentdb:
                res = rec
                break
            for i in res:
                if isinstance(i,Number):
                    varTypes.append(Number)
                else:
                    varTypes.append(unicode)
            self.currentdb.close()
        return zip(varNames, varTypes)


class CsvConector(object):
    def __init__(self, fname, **kwargs):
        """
        Opens a CSV file
        :param fname: Name of the csv file to be opened
        :return: None
        """
        db = pd.read_csv(fname, **kwargs)
        self.currentdb = db
        self.currentdbname = fname
        self.tables = tables = [fname]
        self.dbstructure = OrderedDict()
        self._inspect_db()
        #TODO: make sure that all actions related with reading from databases works for csv as

    def _inspect_db(self):
        self._get_var_types()
        self.dbstructure[self.tables[0]] = self.currentVarTypes

    def _get_var_types(self):
        """
        Detects the types of the variables
        """
        varNames = vnames = self.currentdb.columns.tolist()
        varTypes = []
        for v in vnames:
            if isinstance(self.currentdb[v][0], Number):
                varTypes.append(Number)
            else:
                varTypes.append(unicode)
        self.currentVarTypes = dict(zip(vnames, self.currentdb.dtypes.tolist()))

    def get_data_frame(self, table=None):
        """
        Returns a Data Frame with the data.
        :param table: In the case of a CSV, there is no need to specify a table.
        """
        return self.currentdb


class DbfConector(object):
    def __init__(self, fname, **kwargs):
        """
        Opens a DBF file
        :param fname: Name of the dbf file to be opened
        :return: None
        """
        db = dbf.Table(fname)
        self.currentdb = db
        self.currentdbname = fname
        self.tables = [fname]
        self.dbstructure = OrderedDict()
        self._inspect_db()

    def _inspect_db(self):
        for t in self.tables:
            self._get_var_types()
            self.dbstructure[t] = self.currentVarTypes

    def _get_var_types(self):
        """
        Detects the types of the variables
        """
        self.currentdb.open()
        varNames = vnames = self.currentdb.field_names
        varTypes = []
        for rec in self.currentdb:
            res = rec
            break
        for i in res:
            if isinstance(i, Number):
                varTypes.append(Number)
            else:
                varTypes.append(unicode)
        self.currentdb.close()
        self.currentVarTypes = dict(zip(vnames, varTypes))

    def get_data_frame(self, table=None):
        """
        Returns a Data Frame with the data.
        :param table: In the case of a DBF, there is no need to specify a table.
        """
        self.currentdb.open()
        cols = OrderedDict(zip(self.currentdb.field_names, [[]] * len(self.currentVarTypes)))
        for record in self.currentdb:
            for i, field in enumerate(cols.iterkeys()):
                cols[field].append(record[i])
        self.currentdb.close()
        df = pd.DataFrame(cols)
        return df


class MySQLConector(object):
    def __init__(self, host, database, user, passwd, port):
        """
        Opens a MySQL database
        :param port:
        :param host:
        :param database:
        :param user:
        :param passwd:
        """
        db = SQLSoup('mysql://%s:%s@%s:%s/%s' % (user, passwd, host, port,
                                                 database))
        self.currentdb = db
        self.currentdbname = database
        self.tables = db.bind.table_names()
        self.dbstructure = OrderedDict()
        self._inspect_db()

    def _inspect_db(self):
        for t in self.tables:
            self._get_var_types()
            self.dbstructure[t] = self.currentVarTypes

    def _get_var_types(self, table):
        """
        Detects the types of the variables
        """
        sql = 'SELECT * FROM %s LIMIT 1' % table
        q = self.currentdb.bind.execute(sql)
        vnames = q.keys()
        varTypes = []
        res = q.fetchone()
        if res != None:
        #only if there are rows in the table
            for i, n in zip(res, vnames):
                if isinstance(i, Number):
                    varTypes.append(Number)
                else:
                    varTypes.append(unicode)
        self.currentdb.flush()
        self.currentVarTypes = self.currentVarTypes = dict(zip(vnames, varTypes))

    def get_data_frame(self, table=None, offset=0, limit=1000):
        """
        Returns a Data Frame with the data.
        :param table: In the case of a DBF, there is no need to specify a table.
        """
        sql = 'SELECT * FROM %s LIMIT %s OFFSET %s' % (table, limit, offset)
        q = self.currentdb.bind.execute(sql)
        vnames = q.keys()
        cols = OrderedDict(zip(vnames, [[]] * len(self.currentVarTypes)))
        for record in q.fetchall():
            for i, field in enumerate(cols.iterkeys()):
                cols[field].append(record[i])
        self.currentdb.close()
        df = pd.DataFrame(cols)
        return df


class MonetdbConector(object):
    def __init__(self, host, database, user, passwd, port=50000):
        """
        Opens a Monetdb database
        :param port:
        :param host:
        :param database:
        :param user:
        :param passwd:
        """
        connection = monetdb.sql.connect(username=user, password=passwd, hostname=host, database=database, port=port)
        db = connection.cursor()
        self.currentdb = db
        self.currentdbname = database
        self.tables = db.bind.table_names()
        self.dbstructure = OrderedDict()
        self._inspect_db()

    def _inspect_db(self):
        for t in self.tables:
            self._get_var_types()
            self.dbstructure[t] = self.currentVarTypes

    def _get_var_types(self, table):
        """
        Detects the types of the variables
        """
        sql = 'SELECT * FROM %s LIMIT 1' % table
        q = self.currentdb.execute(sql)
        vnames = q.keys()
        varTypes = []
        res = q.fetchone()
        if res != None:
        #only if there are rows in the table
            for i, n in zip(res, vnames):
                if isinstance(i, Number):
                    varTypes.append(Number)
                else:
                    varTypes.append(unicode)
        self.currentdb.flush()
        self.currentVarTypes = self.currentVarTypes = dict(zip(vnames, varTypes))

    def get_data_frame(self, table=None, offset=0, limit=1000):
        """
        Returns a Data Frame with the data.
        :param table: In the case of a DBF, there is no need to specify a table.
        """
        sql = 'SELECT * FROM %s LIMIT %s OFFSET %s' % (table, limit, offset)
        q = self.currentdb.execute(sql)
        vnames = q.keys()
        cols = OrderedDict(zip(vnames, [[]] * len(self.currentVarTypes)))
        for record in q.fetchall():
            for i, field in enumerate(cols.iterkeys()):
                cols[field].append(record[i])
        self.currentdb.close()
        df = pd.DataFrame(cols)
        return df


class SQLiteConector(object):
    def __init__(self, fname):
        """
        Opens a SQLite database
        :param fname: File name holding the database (full path)
        """
        db = SQLSoup('sqlite:///%s' % fname)
        self.currentdb = db
        self.currentdbname = fname
        self.tables = db.bind.table_names()
        self.dbstructure = OrderedDict()
        self._inspect_db()

    def _inspect_db(self):
        for t in self.tables:
            self._get_var_types()
            self.dbstructure[t] = self.currentVarTypes

    def _get_var_types(self, table):
        """
        Detects the types of the variables
        """
        sql = 'SELECT * FROM %s LIMIT 1' % table
        q = self.currentdb.bind.execute(sql)
        vnames = q.keys()
        varTypes = []
        res = q.fetchone()
        if res is not None:
        #only if there are rows in the table
            for i, n in zip(res, vnames):
                if isinstance(i, Number):
                    varTypes.append(Number)
                else:
                    varTypes.append(unicode)
        self.currentdb.flush()
        self.currentVarTypes = self.currentVarTypes = dict(zip(vnames, varTypes))

    def get_data_frame(self, table=None, offset=0, limit=1000):
        """
        Returns a Data Frame with the data.
        :param table: In the case of a DBF, there is no need to specify a table.
        """
        sql = 'SELECT * FROM %s LIMIT %s OFFSET %s' % (table, limit, offset)
        q = self.currentdb.bind.execute(sql)
        vnames = q.keys()
        cols = OrderedDict(zip(vnames, [[]] * len(self.currentVarTypes)))
        for record in q.fetchall():
            for i, field in enumerate(cols.iterkeys()):
                cols[field].append(record[i])
        self.currentdb.close()
        df = pd.DataFrame(cols)
        return df

_supported_kinds = {
    'csv': CsvConector,
    'dbf': DbfConector,
    'sqlite': '',
    'mysql': MySQLConector,
    'monetdb': MonetdbConector,
    }
